package com.wmm.dao;

import com.wmm.bean.Classes;
import com.wmm.bean.Student;
import com.wmm.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class ClassDaoImp implements ClassDao{
    @Override
    public boolean addClasses(Classes classes) {

        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            String sql = "INSERT classes VALUES(?,?,?,?,?,?)";

            Object[] parpm = {classes.getClassNo(),classes.getClassName(),classes.getGrade(),
                                classes.getClassTeacher(),classes.getClassSlogan(),classes.getClassNumber()};
            //3.执行update, 不需要传入连接对象
            int num = qr.update(sql,parpm);
            if (num>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean deleteClasses(String classNo) {
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            String sql = "delete from classes where classNo = ?";

            Object[] parpm = {classNo};
            //3.执行update, 不需要传入连接对象
            int num = qr.update(sql,parpm);
            if (num>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateClasses(Classes classes) {
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            String sql = "update classes set " +
                    "className = ?,grade=?,classTeacher=?,classSlogan=?,classNumber = ? where classNo = ?";

            Object[] parpm = {  classes.getClassName(),classes.getGrade(),
                                classes.getClassTeacher(),classes.getClassSlogan(),
                                classes.getClassNumber(),classes.getClassNo()};
            //3.执行update, 不需要传入连接对象
            int num = qr.update(sql,parpm);
            if (num>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public List<Classes> queryClasses(String classNo, String className) {
        String sql = null;
        List<Classes> query = null;
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            if (null != classNo && null == className){
                sql = "select * from classes where classNo = ?";
                Object[] parpm = {classNo};
                query = qr.query(sql, new BeanListHandler<Classes>(Classes.class),parpm);
            }else if (null == classNo && null != className){
                sql = "select * from classes where className = ?";
                Object[] parpm = {className};
                query = qr.query(sql, new BeanListHandler<Classes>(Classes.class),parpm);
            }else if (null != classNo && null != className){
                sql = "select * from classes where classNo = ? and className =?";
                Object[] parpm = {classNo,className};
                query = qr.query(sql, new BeanListHandler<Classes>(Classes.class),parpm);
            }
            if (null != query){
                return query;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean addNumber(String classNo) {
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            String sql = "update classes set classNumber = classNumber + 1 where classNo = ?";

            Object[] parpm = {classNo};
            //3.执行update, 不需要传入连接对象
            int num = qr.update(sql,parpm);
            if (num>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean reducceNumber(String classNo) {
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            String sql = "update classes set classNumber = classNumber - 1 where classNo = ?";

            Object[] parpm = {classNo};
            //3.执行update, 不需要传入连接对象
            int num = qr.update(sql,parpm);
            if (num>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public List<Classes> findByPage(int start, int rows) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);
            String sql = "select * from classes limit ?,?";
            Object[] parpm = {start,rows};
            List<Classes> query = qr.query(sql, new BeanListHandler<Classes>(Classes.class),parpm);
            if (null != query){
                return query;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int findTotalCount() {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);
            String sql = "select count(*) from classes";
            Object obj = qr.query(sql, new ScalarHandler());
            return Integer.parseInt(String.valueOf(obj));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Classes> findqueryByPage(int start, int rows, String classNo, String className) {
        String sql = null;
        List<Classes> query = null;

        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            if (null != classNo && null == className) {    //学号不为空但是姓名为空的条件下
                sql = "select * from classes where classNo = ? limit ?,?";

                Object[] parpm = {classNo,start,rows};
                query = qr.query(sql, new BeanListHandler<Classes>(Classes.class),parpm);

            } else if (null == classNo && null != className) {     //学号为空但是姓名不为空的条件下
                sql = "select * from classes where className = ? limit ?,?";

                Object[] parpm = {className,start,rows};
                query = qr.query(sql, new BeanListHandler<Classes>(Classes.class),parpm);
            } else if(null != classNo && null != className){                                            //学号和姓名都不为空，根据两个条件查询
                sql = "select * from classes where classNo = ? and className = ? limit ?,? ";

                Object[] parpm = {classNo,className,start,rows};
                query = qr.query(sql, new BeanListHandler<Classes>(Classes.class),parpm);

            }else{
                sql = "select * from classes limit ?,? ";

                Object[] parpm = {start,rows};
                query = qr.query(sql, new BeanListHandler<Classes>(Classes.class),parpm);
            }


            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int findqueryTotalCount(String classNo, String className) {
        String sql = null;
        Object obj = null;
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            if (null != classNo && null == className) {    //学号不为空但是姓名为空的条件下
                sql = "select count(*) from classes where classNo = ?";

                Object[] parpm = {classNo};
                obj = qr.query(sql, new ScalarHandler<>(), parpm);

            } else if (null == classNo && null != className) {     //学号为空但是姓名不为空的条件下
                sql = "select count(*) from classes where className = ?";

                Object[] parpm = {className};
                obj = qr.query(sql, new ScalarHandler<>(), parpm);

            } else if (null != classNo && null != className) {                                            //学号和姓名都不为空，根据两个条件查询
                sql = "select count(*) from classes where classNo = ? and className = ?";

                Object[] parpm = {classNo,className};
                obj = qr.query(sql, new ScalarHandler<>(), parpm);
            }else{
                sql = "select count(*) from classes";
                obj = qr.query(sql, new ScalarHandler());
            }
            return Integer.parseInt(String.valueOf(obj));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
